﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Bitirme.DAL.Entities;
using System.Data.SqlClient;
using Bitirme.DAL.Utils;
using System.Data;


namespace Bitirme.DAL.Data_Access
{
    public static class ProjectManagerCollection
    {
        public static int InsertProjectManager(ProjectManager manager)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            //SqlParameter parID = null;
            SqlParameter parName = null;
            SqlParameter parSurname = null;
            SqlParameter parEMail = null;
            SqlParameter parSalaryPerDay = null;
            SqlParameter parHourPerDay = null;
            SqlParameter parReturnValue = null;


            int insertedProjectManagerID = -1;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("InsertProjectManager", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??


                //parID = new SqlParameter();
                //parID.ParameterName = "@ID";
                //parID.Value = manager.ID;
                //parID.SqlDbType = SqlDbType.Int;

                parName = new SqlParameter();
                parName.ParameterName = "@name";
                parName.Value = manager.Name;
                parName.SqlDbType = SqlDbType.VarChar;

                parSurname = new SqlParameter();
                parSurname.ParameterName = "@surname";
                parSurname.Value = manager.SurName;
                parSurname.SqlDbType = SqlDbType.VarChar;

                parEMail = new SqlParameter();
                parEMail.ParameterName = "@email";
                parEMail.Value = manager.Email;
                parEMail.SqlDbType = SqlDbType.VarChar;

                parSalaryPerDay = new SqlParameter();
                parSalaryPerDay.ParameterName = "@salaryperday";
                parSalaryPerDay.Value = manager.SalaryPerDay;
                parSalaryPerDay.SqlDbType = SqlDbType.Int;

                parHourPerDay = new SqlParameter();
                parHourPerDay.ParameterName = "@hourperday";
                parHourPerDay.Value = manager.HourPerDay;
                parHourPerDay.SqlDbType = SqlDbType.Int;

                parReturnValue = new SqlParameter();
                parReturnValue.Direction = ParameterDirection.ReturnValue;
                parReturnValue.ParameterName = "@ID";

                //cmd.Parameters.Add(parID);
                cmd.Parameters.Add(parName);
                cmd.Parameters.Add(parSurname);
                cmd.Parameters.Add(parEMail);
                cmd.Parameters.Add(parHourPerDay);
                cmd.Parameters.Add(parSalaryPerDay);
                cmd.Parameters.Add(parReturnValue);

                myConnection.Open();
                cmd.ExecuteScalar();
                Int32.TryParse(parReturnValue.Value.ToString(), out insertedProjectManagerID);
            }
            catch (SqlException e)
            {

                insertedProjectManagerID = -1;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }


            return insertedProjectManagerID;
        }
        public static Boolean UpdateProjectManager(ProjectManager manager)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parID = null;
            SqlParameter parName = null;
            SqlParameter parSurname = null;
            SqlParameter parEMail = null;
            SqlParameter parSalaryPerDay = null;
            SqlParameter parHourPerDay = null;


            bool isupdatedProjectManager = false;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("UpdateProjectManagerByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??


                parID = new SqlParameter();
                parID.ParameterName = "@ID";
                parID.Value = manager.ID;
                parID.SqlDbType = SqlDbType.Int;

                parName = new SqlParameter();
                parName.ParameterName = "@name";
                parName.Value = manager.Name;
                parName.SqlDbType = SqlDbType.VarChar;

                parSurname = new SqlParameter();
                parSurname.ParameterName = "@surname";
                parSurname.Value = manager.SurName;
                parSurname.SqlDbType = SqlDbType.VarChar;

                parEMail = new SqlParameter();
                parEMail.ParameterName = "@email";
                parEMail.Value = manager.Email;
                parEMail.SqlDbType = SqlDbType.VarChar;

                parSalaryPerDay = new SqlParameter();
                parSalaryPerDay.ParameterName = "@salaryperday";
                parSalaryPerDay.Value = manager.SalaryPerDay;
                parSalaryPerDay.SqlDbType = SqlDbType.Int;

                parHourPerDay = new SqlParameter();
                parHourPerDay.ParameterName = "@hourperday";
                parHourPerDay.Value = manager.HourPerDay;
                parHourPerDay.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parID);
                cmd.Parameters.Add(parName);
                cmd.Parameters.Add(parSurname);
                cmd.Parameters.Add(parEMail);
                cmd.Parameters.Add(parHourPerDay);
                cmd.Parameters.Add(parSalaryPerDay);

                isupdatedProjectManager = true;
                myConnection.Open();
                cmd.ExecuteScalar();
            }
            catch (SqlException e)
            {
                isupdatedProjectManager = false;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }
            return isupdatedProjectManager;
        }
        public static Boolean DeleteProjectManagerByID(int projectmanagerID)
        {
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parProjectManagerID = null;
            bool isDeleteSuccessful = false;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("DeleteProjectManagerByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parProjectManagerID = new SqlParameter();
                parProjectManagerID.ParameterName = "@ID";
                parProjectManagerID.Value = projectmanagerID;
                parProjectManagerID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parProjectManagerID);


                myConnection.Open();

                cmd.ExecuteScalar();
                isDeleteSuccessful = true;
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
                isDeleteSuccessful = false;
            }

            finally
            {

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return isDeleteSuccessful;
        }
        public static ProjectManager GetProjectManagerByID(int projectmanagerID)
        {
            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            SqlParameter parProjectManagerID = null;
            ProjectManager projectManager = new ProjectManager();

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetProjectManagerByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parProjectManagerID = new SqlParameter();
                parProjectManagerID.ParameterName = "@ID";
                parProjectManagerID.Value = projectmanagerID;
                parProjectManagerID.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parProjectManagerID);

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    projectManager = ReadProjectManagerWithReferences(myReader);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return projectManager;
        }

        private static ProjectManager ReadProjectManagerWithReferences(SqlDataReader myReader)
        {
            ProjectManager projectmanager = new ProjectManager();
            /*
            int ID;
            Int32.TryParse(SQLUtils.NVL(myReader["ID"], 0).ToString(), out ID);
            projectmanager.ID = ID;

            project.Inventories = InventoryManager.GetInventoriesByProjectID(project.ID);

            projectmanager.Name = SQLUtils.NVL(myReader["NAME"], String.Empty).ToString();
            projectmanager.SurName = SQLUtils.NVL(myReader["SURNAME"], String.Empty).ToString();
            projectmanager.Email = SQLUtils.NVL(myReader["EMAIL"], String.Empty).ToString();

            Double salaryPerDay;
            Double.TryParse(SQLUtils.NVL(myReader["SALARY_PER_DAY"], 0).ToString(), out salaryPerDay);
            projectmanager.SalaryPerDay = salaryPerDay;

            int hoursPerDay;
            Float.TryParse(SQLUtils.NVL(myReader["HOURS_PER_DAY"], 0).ToString(), out hoursPerDay);
            projectmanager.HourPerDay = hoursPerDay;


            //start end dates--
            if (myReader["EndDate"] == DBNull.Value)
            {
                project.EndDate = null;
            }
            else
            {
                project.EndDate = Convert.ToDateTime(myReader["EndDate"].ToString());
            }

            if (myReader["StartDate"] == DBNull.Value)
            {
                project.StartDate = null;
            }
            else
            {
                project.StartDate = Convert.ToDateTime(myReader["StartDate"].ToString());
            }
            //period
            short period;
            short.TryParse(SQLUtils.NVL(myReader["Period"], 0).ToString(), out period);
            switch (period)
            {
                case 0:
                    project.Period = Period.None;
                    break;
                case 1:
                    project.Period = Period.AM;
                    break;
                case 2:
                    project.Period = Period.PM;
                    break;
                case 3:
                    project.Period = Period.FullTime;
                    break;
                default:
                    project.Period = Period.None;
                    break;
            }
            //state
            short state;
            short.TryParse(SQLUtils.NVL(myReader["State"], 0).ToString(), out state);
            switch (state)
            {

                case 0:
                    project.State = ProjectState.None;
                    break;
                case 1:
                    project.State = ProjectState.Reserved;
                    break;
                case 2:
                    project.State = ProjectState.Set;
                    break;
                case 3:
                    project.State = ProjectState.Canceled;
                    break;
                default:
                    project.State = ProjectState.None;
                    break;
            }

            short methodCount;
            short.TryParse(SQLUtils.NVL(myReader["MethodCount"], 0).ToString(), out methodCount);
            project.MethodCount = methodCount;

            project.Details = SQLUtils.NVL(myReader["Details"], String.Empty).ToString();

            project.InsertDate = Convert.ToDateTime(SQLUtils.NVL(myReader["InsertDate"], DateTime.MaxValue).ToString());
            project.LastUpdateDate = Convert.ToDateTime(SQLUtils.NVL(myReader["LastUpdateDate"], DateTime.MaxValue).ToString());

            int creatorUserID;
            Int32.TryParse(SQLUtils.NVL(myReader["CreatorUserID"], 0).ToString(), out creatorUserID);
            project.CreatorUser.ID = creatorUserID;

            int updaterUserID;
            Int32.TryParse(SQLUtils.NVL(myReader["UpdaterUserID"], 0).ToString(), out updaterUserID);
            project.UpdaterUser.ID = updaterUserID;

            short status;
            short.TryParse(SQLUtils.NVL(myReader["Status"], 0).ToString(), out status);
            project.Status = status;
            */
            return projectmanager;

        }
        public static List<ProjectManager> GetAllProjectManagers()
        {
            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            List<ProjectManager> projects = new List<ProjectManager>();

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetAllProjectManagers", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    ProjectManager tempProjectManager = new ProjectManager();

                    int ID;
                    Int32.TryParse(SQLUtils.NVL(myReader["ID"], 0).ToString(), out ID);
                    tempProjectManager.ID = ID;

                    tempProjectManager.Name = SQLUtils.NVL(myReader["Name"], String.Empty).ToString();

                    projects.Add(tempProjectManager);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return projects;
        }

        public static List<ProjectManager> GetAllProjectManagersWithAllAttributes() {

            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            List<ProjectManager> projectManangers = new List<ProjectManager>();

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetAllProjectManagersWithAllAttributes", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    ProjectManager tempProjectManager = new ProjectManager();

                    int ID;
                    Int32.TryParse(SQLUtils.NVL(myReader["ID"], 0).ToString(), out ID);
                    tempProjectManager.ID = ID;

                    tempProjectManager.Name = SQLUtils.NVL(myReader["Name"], String.Empty).ToString();
                    tempProjectManager.SurName = SQLUtils.NVL(myReader["Surname"], String.Empty).ToString();
                    tempProjectManager.Email = SQLUtils.NVL(myReader["EMAIL"], String.Empty).ToString();
                    int hoursperday;
                    Int32.TryParse(SQLUtils.NVL(myReader["HOURS_PER_DAY"], 0).ToString(), out hoursperday);
                    tempProjectManager.HourPerDay = hoursperday;
                    int salaryperday;
                    Int32.TryParse(SQLUtils.NVL(myReader["SALARY_PER_DAY"], 0).ToString(), out salaryperday);
                    tempProjectManager.SalaryPerDay = salaryperday;

                    projectManangers.Add(tempProjectManager);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return projectManangers;
        }
    }
}